package model;

import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.Hashtable;

import controller.MaetecConnexion;

public abstract class Database {

	protected String table_name;
	
	protected int id_table;
	
	
	public void insertRow(String name, String tab[], Hashtable<?, ?> fields) throws SQLException, ClassNotFoundException{
		String sql="INSERT INTO "+name+" SET ";
		sql=sql+this.consructSql(tab, fields,true);
		this.connect().execute(sql);
	}
	
	public void insertRow(String name, PreparedStatement sql) throws SQLException, ClassNotFoundException{
	}
	public void updateRow(String name, String tab[], Hashtable<?, ?> fields, Hashtable<?, ?> fieldsCond) throws SQLException, ClassNotFoundException{
		String sql="UPDATE "+name+" SET ";
		sql=sql+this.consructSql(tab, fields,false);
		sql=sql+" WHERE "+this.consructSql(tab, fieldsCond);
		this.connect().executeUpdate(sql);
	}
	
	public void deleteRow(String name, String tab[], Hashtable<?, ?> fields ) throws SQLException, ClassNotFoundException{
		String sql="DELETE FROM "+name+" WHERE ";
		sql=sql+this.consructSql(tab, fields);
		this.connect().executeUpdate(sql);
	}
	
	private Statement connect() throws SQLException, ClassNotFoundException{
		Statement state= MaetecConnexion.getInstance().createStatement();
		return state;
	}
	
	public PreparedStatement connectprepare(String sql) throws SQLException, ClassNotFoundException{
		PreparedStatement state= MaetecConnexion.getInstance().prepareStatement(sql);
		return state;
	}
	
	public ResultSet selectTable(String name) throws SQLException, ClassNotFoundException{
		String sql="SELECT * FROM "+name;
		return this.connect().executeQuery(sql);
	}
	
	public ResultSet selectRows(String name, String tab[], Hashtable<?, ?> fields) throws SQLException, ClassNotFoundException{
		String sql="SELECT * FROM "+name+" WHERE ";
		sql=sql+this.consructSql(tab, fields);
		return this.connectprepare(sql).executeQuery(sql);
	}
	private String consructSql(String tabs[], Hashtable<?, ?> fields, boolean test){
		String sql = "";
		for(String str:tabs){
			if(fields.containsKey(str)){
				sql=sql+" "+str+"='"+fields.get(str)+"',";
			}else{
				if(test){
					String val="";
					sql=sql+" "+str+"="+(str.toLowerCase()=="id"?0:"'"+val+"'")+",";
				}
			}
		}
		sql=(String) sql.subSequence(0, sql.length()-1);
		return sql;	
	}
	
	private String consructSql(String tabs[], Hashtable<?, ?> fields){
		String sql = "";
		int ii=0;
		for(String str:tabs){
			if(fields.containsKey(str)){
				sql=sql+(ii==0?" ":" AND ")+str+"='"+fields.get(str)+"'";
				ii++;
			}
		}
		return sql;
	}
	
	public String constructPrepareSql(String tabs[], Hashtable<?, ?> fields){
		String sql = "";
		for(String str:tabs){
			if(fields.containsKey(str)){
				//sql=sql+(ii==0?" ":" AND ")+str+"=?";
				sql=sql+" "+str+"=? ,";
			}
		}
		sql=(String) sql.subSequence(0, sql.length()-1);
		return sql;
	}
	
}
